package com.ids.transfer.util;

import java.beans.PropertyDescriptor;
import java.io.PrintStream;
import java.io.StringReader;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import oracle.jdbc.rowset.OracleCachedRowSet;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaClass;
import org.apache.commons.beanutils.DynaProperty;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DbUtils {
	static Class<?> DbUtils;
	protected static Log log = LogFactory.getLog(DbUtils.class);

	static Class class$(String a) {
		try {
			return Class.forName(a);
		} catch (ClassNotFoundException e) {
			throw new NoClassDefFoundError(e.getMessage());
		}
	}

	/**
	 * ������ݼ�ת���ļ���
	 * 
	 * @param conn
	 * @param command
	 * @param parameters
	 * @return
	 */
	public static final Collection getCollection(Connection conn,
			String command, Object[] parameters) {
		List list = null;
		ResultSet resultset = null;
		try {
			resultset = executeQuery(conn, command, parameters);
			list = resultSetToList(resultset);
		} catch (Exception ex) {
			throw new RuntimeException(ex.getMessage() + "(ִ��SQL��ѯ:" + command
					+ "����)", ex);
		} finally {
			try {
				if (resultset != null) {
					close(resultset, resultset.getStatement());
				}
			} catch (Exception ex) {
				log.error(ex);
			}

		}

		return list;
	}

	public static final Collection getCollection(String command,
			Object[] parameters) {
		List list = null;
		ResultSet resultset = null;
		Connection connection = GetConnection();
		try {
			resultset = executeQuery(connection, command, parameters);
			list = resultSetToList(resultset);
		} catch (Exception ex) {
			throw new RuntimeException(ex.getMessage() + "(ִ��SQL��ѯ:" + command
					+ "����)", ex);
		} finally {
			try {
				if (resultset != null) {
					close(resultset, resultset.getStatement(), connection);
				} else
					close(null, null, connection);
			} catch (Exception ex) {
				log.error(ex);
			}

		}

		return list;
	}

	public static final Map getMap(Connection conn, String command) {
		return getMap(conn, command, null);
	}

	/**
	 * ��������ݼ�ת����Map����
	 * 
	 * @param command
	 * @param parameters
	 * @return
	 */
	public static final Map getMap(String command, Object[] parameters) {
		Map map = null;
		ResultSet resultset = null;
		Connection connection = GetConnection();
		try {
			resultset = executeQuery(connection, command, parameters);
			map = resultSetToMap(resultset);
		} catch (Exception ex) {
			throw new RuntimeException(ex.getMessage() + "(ִ��SQL��ѯ:" + command
					+ "����)", ex);
		} finally {
			try {
				if (resultset != null) {
					close(resultset, resultset.getStatement(), connection);
				} else
					close(null, null, connection);
			} catch (Exception ex) {
				log.error(ex);
			}
			close(connection);
		}

		return map;
	}

	public static final Collection getCollection(String command,
			Object[] parameters, Connection connection) {
		List list = null;
		ResultSet resultset = null;
		resultset = executeQuery(connection, command, parameters);
		list = resultSetToList(resultset);

		return list;
	}

	public static final Collection getCollection(String command, Object key) {
		Object[] parameters = new Object[1];
		parameters[0] = key;

		return getCollection(command, parameters);
	}

	public static final Collection getCollection(String command, Object key1,
			Object key2) {
		Object[] parameters = new Object[2];
		parameters[0] = key1;
		parameters[1] = key2;

		return getCollection(command, parameters);
	}

	public static final Collection getCollection(String command, Object key1,
			Object key2, Object key3) {
		Object[] parameters = new Object[3];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;

		return getCollection(command, parameters);
	}

	public static final Collection getCollection(Connection connection,
			String command, Object key1, Object key2, Object key3, Object key4) {
		Object[] parameters = new Object[4];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;

		return getCollection(command, parameters);
	}

	public static final Collection getCollection(String command, Object key1,
			Object key2, Object key3, Object key4, Object key5) {
		Object[] parameters = new Object[5];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;
		parameters[4] = key5;

		return getCollection(command, parameters);
	}

	public static final Collection getCollection(String command, int key) {
		Object[] parameters = new Object[1];
		parameters[0] = new Integer(key);

		return getCollection(command, parameters);
	}

	public static final Collection getCollection(Connection conn, String command) {
		return getCollection(conn, command, null);
	}

	public static final Collection getCollection(String command) {
		return getCollection(command, null);
	}

	public static final Collection getCollectionLike(Connection connection,
			String command, String key) {
		Object[] parameters = new Object[1];
		parameters[0] = new String("%" + key + "%");

		return getCollection(command, parameters);
	}

	/**
	 * ����JDBC����
	 * 
	 * @param as_DBurl
	 * @param as_DBuser
	 * @param as_DBpass
	 * @param as_DBdriver
	 * @return
	 */
	public static final Connection getJdbcConnection(String as_DBurl,
			String as_DBuser, String as_DBpass, String as_DBdriver) {
		try {
			Class.forName(as_DBdriver);
		} catch (ClassNotFoundException ex) {
			throw new RuntimeException("δ�ҵ���ݿ������" + as_DBdriver, ex);
		}

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(as_DBurl, as_DBuser, as_DBpass);
		} catch (SQLException ex) {
			throw new RuntimeException("��ȡ��ݿ�����" + as_DBurl + "����!", ex);
		}

		return conn;
	}

	public static final Connection getJdbcConnection(String defaultRowPrefetch,
			String defaultExecuteBatch) {
		String db_url = Resources.getResource("datasource", "url");

		String db_user = Resources.getResource("datasource", "user");

		String db_pass = Resources.getResource("datasource", "password");

		String db_driver = Resources.getResource("datasource", "driver");
		try {
			Class.forName(db_driver);
		} catch (ClassNotFoundException ex) {
			throw new RuntimeException("δ�ҵ���ݿ������" + db_driver, ex);
		}

		Properties info = new Properties();
		info.put("user", db_user);
		info.put("password", db_pass);
		if (!StrCharUtil.formatNullStr(defaultRowPrefetch).equals("")) {
			info.put("defaultRowPrefetch", defaultRowPrefetch);
		}
		if (!StrCharUtil.formatNullStr(defaultExecuteBatch).equals("")) {
			info.put("defaultExecuteBatch", defaultExecuteBatch);
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(db_url, info);
		} catch (SQLException ex) {
			throw new RuntimeException("��ȡ��ݿ�����" + db_url + "����!", ex);
		}

		return conn;
	}

	public static final Connection getJdbcConnection(String as_DBurl,
			String as_DBuser, String as_DBpass, String as_DBdriver,
			String defaultRowPrefetch, String defaultExecuteBatch) {
		try {
			Class.forName(as_DBdriver);
		} catch (ClassNotFoundException ex) {
			throw new RuntimeException("δ�ҵ���ݿ������" + as_DBdriver, ex);
		}
		Properties info = new Properties();
		info.put("user", as_DBuser);
		info.put("password", as_DBpass);
		if (!StrCharUtil.formatNullStr(defaultRowPrefetch).equals("")) {
			info.put("defaultRowPrefetch", defaultRowPrefetch);
		}
		if (!StrCharUtil.formatNullStr(defaultExecuteBatch).equals(""))
			info.put("defaultExecuteBatch", defaultExecuteBatch);
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(as_DBurl, info);
		} catch (SQLException ex) {
			throw new RuntimeException("��ȡ��ݿ�����" + as_DBurl + "����!", ex);
		}

		return conn;
	}

	public static final Connection getJdbcConnection() {
		Connection conn = null;

		if (conn == null) {
			conn = getJdbcConnection(
					"jdbc:oracle:thin:@172.20.0.166:1521:psmis", "epcrm",
					"epcrm", "oracle.jdbc.driver.OracleDriver");

			System.out.println("create jdbc connection");
		}

		return conn;
	}

	// --------------------------------------
	public static final Object[] ExecuteSP(String stmt, Object key1) {
		Object[] inparams = new Object[1];
		inparams[0] = key1;

		return ExecuteSP(stmt, inparams);
	}

	public static final Object[] ExecuteSP(String stmt, Object key1, Object key2) {
		Object[] inparams = new Object[2];
		inparams[0] = key1;
		inparams[1] = key2;

		return ExecuteSP(stmt, inparams);
	}

	public static final Object[] ExecuteSP(String stmt, Object key1,
			Object key2, Object key3) {
		Object[] inparams = new Object[3];
		inparams[0] = key1;
		inparams[1] = key2;
		inparams[2] = key3;

		return ExecuteSP(stmt, inparams);
	}

	public static final Object[] ExecuteSP(String stmt, Object key1,
			Object key2, Object key3, Object key4) {
		Object[] inparams = new Object[4];
		inparams[0] = key1;
		inparams[1] = key2;
		inparams[2] = key3;
		inparams[3] = key4;

		return ExecuteSP(stmt, inparams);
	}

	public static final Object[] ExecuteSP(String stmt, Object key1,
			Object key2, Object key3, Object key4, Object key5) {
		Object[] inparams = new Object[5];
		inparams[0] = key1;
		inparams[1] = key2;
		inparams[2] = key3;
		inparams[3] = key4;
		inparams[4] = key5;

		return ExecuteSP(stmt, inparams);
	}

	public static final Object[] ExecuteSP(String stmt, Object[] inparams) {
		Connection conn = GetConnection();

		return ExecuteSP(stmt, inparams, conn);
	}

	public static final Object[] ExecuteSP(String stmt,
			Object[] inparams, Connection connection){
    
		int i = 1;
    //����ִ�� SQL �洢��̵Ľӿڡ�
    //JDBC API �ṩ��һ���洢��� SQL ת���﷨��
    //���﷨��������� RDBMS ʹ�ñ�׼��ʽ���ô洢��̡�
    CallableStatement st = null;
    SpParameter sp = null;
    SpParameter outp = null;
    
    try
    {
    //provides methods for setting up its IN and OUT parameters
      st = connection.prepareCall(stmt);

      int oupcount = 0;

      for (i = 0; i < inparams.length; i++)
      {
        if ((inparams[i] instanceof SpParameter)) {
          sp = (SpParameter)inparams[i];

          if (sp.GetDirection().toUpperCase() == "IN")
          {
            switch (sp.GetDataType())
            {
            case 4:
            case 6:
              st.setLong(i + 1, Long.parseLong(sp.GetDataValue()));
              break;
            case 91:
              st.setDate(i + 1, Date.valueOf(sp.GetDataValue()));

              break;
            default:
              st.setString(i + 1, sp.GetDataValue());
              break;
            }
          }else {
        	  //out
            st.registerOutParameter(i + 1, sp.GetDataType());
            oupcount += 1;
          }
        } else {
          st.setObject(i + 1, inparams[i]);
        }
      }//end for
      st.execute();
      i = 1;
      //Object[] arrayOfObject1=null;
      Object[] outps = null;
      if (oupcount > 0)
      {
        outps = new Object[oupcount];
        int j = 0;
        for (i = 0; i < inparams.length; i++)
        {
          if (!(inparams[i] instanceof SpParameter))
            continue;
          sp = (SpParameter)inparams[i];

          if (sp.GetDirection().toUpperCase() == "IN")
            continue;
          
          int dataType = sp.GetDataType();
          String Direction = sp.GetDirection();
          String Name = sp.GetName();
          String StrDataValue;
          switch (dataType){
          
	          case 6:
	            StrDataValue = String.valueOf(st.getLong(i + 1));
	            break;
	          case 4:
	            StrDataValue = String.valueOf(st.getInt(i + 1));
	            break;
	          case 91:
	            StrDataValue = st.getDate(i + 1).toString();
	            break;
	          default:
	            StrDataValue = st.getString(i + 1);
          }

          sp.setDataValue(StrDataValue);
          outps[(j++)] = StrDataValue;
        }
        //arrayOfObject1 = outps;
      }
      return outps;
      //outps = null;
    }catch (SQLException ex){
      
      throw new RuntimeException("ִ�д洢��̣�" + stmt + "����", ex);
    }finally{
    	
      if (st != null){
    	  
        try{
          st.close();
        }catch (Exception ex) {
          log.error(ex);
        }
      }
    }
  }

	

	public static final Connection GetConnection() {
		String dbType = null;
		dbType = Resources.getResource("datasource", "datasource_type");
		String db_user;
		if ((dbType != null) && (dbType.equalsIgnoreCase("jdbc.db"))) {
			String db_url = null;
			db_user = null;
			String db_pass = null;
			String db_driver = null;
			try {
				db_url = Resources.getResource("datasource", "url");

				db_user = Resources.getResource("datasource", "user");

				db_pass = Resources.getResource("datasource", "password");

				db_driver = Resources.getResource("datasource", "driver");

				return getJdbcConnection(db_url, db_user, db_pass, db_driver);
			} catch (Exception E) {
				throw new RuntimeException("get_connection_error");
			}

		}

		String dbJndiName = null;
		try {
			dbJndiName = Resources.getResource("jndi", "jndi.db");

			if ((dbJndiName == null) || (dbJndiName.equals(""))) {
				dbJndiName = "jdbc/epcrmdb";
			}

			return GetConnection(dbJndiName);
		} catch (Exception E) {
		}
		throw new RuntimeException("get_connection_error"+dbJndiName);
	}

	public static final Connection GetConnection(String as_jndiName) {
		DataSource ds = null;
		try {
			Context ic = new InitialContext();
			ds = (DataSource) ic.lookup(as_jndiName);
		} catch (NamingException ex) {
			throw new RuntimeException("����:" + as_jndiName + "���Ҵ���", ex);
		}
		Connection conn = null;
		try {
			conn = ds.getConnection();
		} catch (Exception ex) {
			throw new RuntimeException("�����ӳ�:" + as_jndiName + "��ȡ���Ӵ���", ex);
		}

		return conn;
	}


	public static final ResultSet executeQuery(Connection connection,
			String command, Object[] parameters) {
		Statement statement = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			if ((parameters == null) || (parameters.length == 0)) {
				statement = connection.createStatement();

				resultSet = statement.executeQuery(command);
			} else {

				preparedStatement = connection.prepareStatement(command);

				for (int i = 0; i < parameters.length; i++) {
					preparedStatement.setObject(i + 1, parameters[i]);
				}

				resultSet = preparedStatement.executeQuery();
			}
		} catch (Exception ex) {
			throw new RuntimeException("EXECUTE ERROR:" + command + "AS:", ex);
		}

		return resultSet;
	}

	public static final ResultSet executeQuery(Connection connection,
			String command, Object key) {
		Object[] parameters = new Object[1];
		parameters[0] = key;

		return executeQuery(connection, command, parameters);
	}

	public static final ResultSet executeQuery(Connection connection,
			String command, Object key1, Object key2) {
		Object[] parameters = new Object[2];
		parameters[0] = key1;
		parameters[1] = key2;

		return executeQuery(connection, command, parameters);
	}

	public static final ResultSet executeQuery(Connection connection,
			String command, Object key1, Object key2, Object key3) {
		Object[] parameters = new Object[3];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;

		return executeQuery(connection, command, parameters);
	}

	public static final ResultSet executeQuery(Connection connection,
			String command, Object key1, Object key2, Object key3, Object key4) {
		Object[] parameters = new Object[4];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;

		return executeQuery(connection, command, parameters);
	}

	public static final ResultSet executeQuery(Connection connection,
			String command, Object key1, Object key2, Object key3, Object key4,
			Object key5) {
		Object[] parameters = new Object[5];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;
		parameters[4] = key5;

		return executeQuery(connection, command, parameters);
	}

	public static final ResultSet executeQuery(Connection connection,
			String command, int key) {
		Object[] parameters = new Object[1];
		parameters[0] = new Integer(key);

		return executeQuery(connection, command, parameters);
	}

	public static final ResultSet executeQuery(Connection connection,
			String command) {
		return executeQuery(connection, command, null);
	}

	public static final ResultSet executeQueryLike(Connection connection,
			String command, String key) {
		Object[] parameters = new Object[1];
		parameters[0] = new String("%" + key + "%");

		return executeQuery(connection, command, parameters);
	}

	public static final int executeUpdate(Connection connection, String command) {
		return executeUpdate(connection, command, null);
	}

	public static final int executeUpdate(Connection connection,
			String command, Object key) {
		Object[] parameters = new Object[1];
		parameters[0] = key;

		return executeUpdate(connection, command, parameters);
	}

	public static final int executeUpdate(Connection connection,
			String command, int key) {
		Object[] parameters = new Object[1];
		parameters[0] = new Integer(key);

		return executeUpdate(connection, command, parameters);
	}

	public static final int executeUpdate(Connection connection,
			String command, Object[] parameters) {
		PreparedStatement statement = null;
		int result = 0;
		try {
			statement = connection.prepareStatement(command);

			if (parameters != null) {
				for (int i = 0; i < parameters.length; i++) {
					if (parameters[i] == null)
						parameters[i] = "";
					if ((parameters[i] instanceof String)) {
						String paraStr = (String) parameters[i];
						if (paraStr.length() > 255) {
							statement
									.setCharacterStream(i + 1,
											new StringReader(paraStr),
											paraStr.length());
						} else
							statement.setString(i + 1, paraStr);
					} else {
						statement.setObject(i + 1, parameters[i]);
					}
				}
			}
			result = statement.executeUpdate();
			//connection.commit();
		} catch (SQLException ex) {
			throw new RuntimeException("ִ�и�����䣺" + command + "����", ex);
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (Exception ex) {
					log.error(ex);
				}

			}

		}

		return result;
	}


	public static Collection getCollectionObject(Class clazz, String stmt) {
		return getCollectionObject(clazz, stmt, null);
	}

	public static Collection getCollectionObject(Class clazz, String stmt,
			Object key1) {
		Object[] params = new Object[1];
		params[0] = key1;

		return getCollectionObject(clazz, stmt, params);
	}

	public static Collection getCollectionObject(Class clazz, String stmt,
			Object key1, Object key2) {
		Object[] params = new Object[2];
		params[0] = key1;
		params[1] = key2;

		return getCollectionObject(clazz, stmt, params);
	}

	public static Collection getCollectionObject(Class clazz, String stmt,
			Object key1, Object key2, Object key3) {
		Object[] params = new Object[3];
		params[0] = key1;
		params[1] = key2;
		params[2] = key3;

		return getCollectionObject(clazz, stmt, params);
	}

	public static Map getMapObject(Class clazz, String stmt, String keycols) {
		return getMapObject(clazz, stmt, keycols, null);
	}

	public static Map getMapObject(Class clazz, String stmt, String keycols,
			Object key1) {
		Object[] params = new Object[1];
		params[0] = key1;

		return getMapObject(clazz, stmt, keycols, params);
	}

	public static Map getMapObject(Class clazz, String stmt, String keycols,
			Object key1, Object key2) {
		Object[] params = new Object[2];
		params[0] = key1;
		params[1] = key2;

		return getMapObject(clazz, stmt, keycols, params);
	}

	public static void setProperties(Object bean, Map properties) {
		if ((bean == null) || (properties == null)) {
			return;
		}
		Iterator names = properties.keySet().iterator();

		while (names.hasNext()) {
			String name = (String) names.next();

			if (name != null) {
				PropertyDescriptor descriptor = null;
				try {
					descriptor = PropertyUtils
							.getPropertyDescriptor(bean, name);
				} catch (Exception ex) {
					throw new RuntimeException("ȡ����������" + name + "����!", ex);
				}

				if (descriptor != null) {
					Method setter = descriptor.getWriteMethod();

					if (setter != null) {
						Object[] args = new Object[1];
						args[0] = properties.get(name);
						try {
							setter.invoke(bean, args);
						} catch (Exception ex) {
							throw new RuntimeException(
									"�������÷���:" + name + "����", ex);
						}
					}
				}
			}
		}
	}

	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		if (rs != null) {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

		if (stmt != null) {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void close(ResultSet rs, Statement stmt) {
		close(rs, stmt, null);
	}

	public static void close(ResultSet rs) {
		if (rs != null) {
			try {
				close(rs, rs.getStatement());
			} catch (Exception ex) {
				log.error(ex);
			}
		}
	}

	public static void close(Connection conn) {
		if (conn != null) {
			close(null, null, conn);
		}
	}

	public static void close(ResultSet rs, Connection conn) {
		try {
			if (rs != null) {
				close(rs, rs.getStatement(), conn);
			} else {
				close(null, null, conn);
			}
		} catch (Exception ex) {
			log.error(ex);
		}
	}

	public static void main(String[] arg) {
	}

	public static void printResultSet(ResultSet rs) throws SQLException {
		if (rs == null) {
			return;
		}
		ResultSetMetaData md = rs.getMetaData();
		int columnCount = md.getColumnCount();

		while (rs.next()) {
			for (int i = 1; i <= columnCount; i++) {
				System.out.print("columnName=" + md.getColumnName(i)
						+ ",value=" + rs.getString(i));
			}

			System.out.println("");
		}
	}

	public static List resultSetToList(ResultSet rs) {
		try {
			if (rs == null) {
				return Collections.EMPTY_LIST;
			}

			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			List list = new ArrayList();
			int i;
			while (rs.next()) {
				Map rowData = new HashMap(columnCount);

				for (i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i).toLowerCase(),
							rs.getObject(i));
					if ((md.getColumnType(i) == 12)
							|| (md.getColumnType(i) == 1)) {
						rowData.put(md.getColumnName(i).toLowerCase(),
								StrCharUtil.formatNullStr((String) rs
										.getObject(i)));
					}
				}

				list.add(rowData);
			}

			return list;
		} catch (Exception ex) {
			throw new RuntimeException("����ѯ���תΪLISTִ�в������!", ex);
		}
	}

	public static List resultSetToList(ResultSet rs, Hashtable fields) {
		try {
			if (rs == null) {
				return Collections.EMPTY_LIST;
			}

			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			List list = new ArrayList();
			int i;
			while (rs.next()) {
				Map rowData = new HashMap(columnCount);

				for (i = 1; i <= columnCount; i++) {
					Object colkey;
					if (fields == null)
						colkey = md.getColumnName(i).toLowerCase();
					else
						colkey = fields.get(md.getColumnName(i).toLowerCase());
					if (rs.getObject(i) != null)
						rowData.put(colkey, rs.getObject(i));
					else {
						rowData.put(colkey, "");
					}
				}

				list.add(rowData);
			}

			return list;
		} catch (Exception ex) {
			throw new RuntimeException("ERROR", ex);
		}
	}

	/**
	 * tableName and where 
	 * @param tableName
	 * @param whereStr
	 * @return
	 */
	public static long getRecordCount(Connection conn,String tableName, String whereStr) {
		long recordCount = 0L;

		if (!whereStr.equals("")) {
			whereStr = " where " + whereStr;
		}

		String sSQL = "select count(*) from " + tableName + whereStr;
		ResultSet rs = executeQuery(conn, sSQL);
		try {
			if (rs.next()) {
				recordCount = rs.getLong(1);
			}
		} catch (Exception ex) {
			throw new RuntimeException("EXECUTE ERROR" + tableName + "as :", ex);
		}


		return recordCount;
	}




	public static long getRecordCount(String sql) {
		long recordCount = 0L;
		Connection conn = GetConnection();
		ResultSet rs = executeQuery(conn, "select count(*) from (" + sql + ")");
		try {
			if (rs.next()) {
				recordCount = rs.getLong(1);
			}
		} catch (Exception ex) {
			throw new RuntimeException("ȡ��¼��" + sql + "����", ex);
		}

		close(rs, conn);

		return recordCount;
	}

	public static long getRecordCount(String sql, Object[] params) {
		long recordCount = 0L;
		Connection conn = GetConnection();
		ResultSet rs = executeQuery(conn, sql, params);
		try {
			if (rs.next()) {
				recordCount = rs.getLong(1);
			}
		} catch (Exception ex) {
			throw new RuntimeException("ȡ��¼��" + sql + "����", ex);
		}

		close(rs, conn);

		return recordCount;
	}

	public static long getRecordCount(Connection conn, String sql,
			Object[] params) {
		long recordCount = 0L;
		//sql=select * from XX where id=?
		ResultSet rs = executeQuery(conn, sql, params);
		try {
			if (rs.next()) {
				recordCount = rs.getLong(1);
			}
		} catch (Exception ex) {
			throw new RuntimeException("ȡ��¼��" + sql + "����", ex);
		}

		return recordCount;
	}

	public static final int executeUpdate(String command, Object[] parameters) {
		int result = 0;
		Connection conn = null;
		try {
			conn = GetConnection();
			executeUpdate(conn, command, parameters);
		} finally {
			try {
				conn.close();
			} catch (Exception ex) {
				log.error(ex);
			}
		}

		return result;
	}

	public static final int executeUpdate(String command, Object key1) {
		Object[] parameters = new Object[1];
		parameters[0] = key1;

		return executeUpdate(command, parameters);
	}

	public static final int executeUpdate(String command, Object key1,
			Object key2) {
		Object[] parameters = new Object[2];
		parameters[0] = key1;
		parameters[1] = key2;

		return executeUpdate(command, parameters);
	}

	public static final int executeUpdate(String command, Object key1,
			Object key2, Object key3) {
		Object[] parameters = new Object[3];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;

		return executeUpdate(command, parameters);
	}

	public static final int executeUpdate(String command, Object key1,
			Object key2, Object key3, Object key4) {
		Object[] parameters = new Object[4];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;

		return executeUpdate(command, parameters);
	}

	public static final int executeUpdate(String command, Object key1,
			Object key2, Object key3, Object key4, Object key5) {
		Object[] parameters = new Object[5];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;
		parameters[4] = key5;

		return executeUpdate(command, parameters);
	}


	public static void executeUpdate(String sDDL) {
		Connection conn = null;
		try {
			conn = GetConnection();
			executeUpdate(conn, sDDL);
		} finally {
			try {
				conn.close();
			} catch (Exception ex) {
				log.error(ex);
			}
		}
	}


	public static OracleCachedRowSet getOracleCachedRowSet(String sSQL) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleCachedRowSet result = null;
		try {
			result = new OracleCachedRowSet();
			conn = GetConnection();
			pstmt = conn.prepareStatement(sSQL);
			rs = pstmt.executeQuery(sSQL);
			result.populate(rs);
		} catch (Exception ex) {
			throw new RuntimeException("ִ�в�ѯ���:" + sSQL + "����", ex);
		} finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (Exception ex) {
				log.error(ex);
			}
		}

		return result;
	}

	public static OracleCachedRowSet getOracleCachedRowSet(Connection conn,
			String sSQL) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleCachedRowSet result = null;
		try {
			result = new OracleCachedRowSet();
			pstmt = conn.prepareStatement(sSQL);
			rs = pstmt.executeQuery(sSQL);
			result.populate(rs);
		} catch (Exception ex) {
			throw new RuntimeException("ִ�в�ѯ���:" + sSQL + "����", ex);
		} finally {
			try {
				rs.close();
				pstmt.close();
			} catch (Exception ex) {
				log.error(ex);
			}
		}

		return result;
	}



	public static void updateTable(String tableName, String[] fieldsName,
			String[] fieldsValue, String whereStr) {
		Connection conn = null;
		String setString = " set ";
		for (int I = 0; I <= fieldsName.length - 1; I++) {
			setString = setString + fieldsName[I] + "='" + fieldsValue[I]
					+ "',";
		}
		setString = setString.substring(0, setString.length() - 1);
		String sSQL = "update " + tableName + setString + whereStr;
		try {
			conn = GetConnection();
			executeUpdate(conn, sSQL);
		} finally {
			try {
				conn.close();
			} catch (Exception ex) {
				log.error(ex);
			}
		}
	}

	public static Map resultSetToMap(ResultSet rs) {
		if (rs == null) {
			return null;
		}
		try {
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			List list = new ArrayList();
			Map rowData;
			if (rs.next()) {
				rowData = new HashMap(columnCount);
				for (int i = 1; i <= columnCount; i++) {
					if (rs.getObject(i) != null)
						rowData.put(md.getColumnName(i).toLowerCase(),
								rs.getObject(i));
					else {
						rowData.put(md.getColumnName(i).toLowerCase(), "");
					}

				}

				return rowData;
			}

			return null;
		} catch (SQLException ex) {
		}
		throw new RuntimeException("����ѯ���תΪHashMapִ�в������!");
	}

	public static final Map getMap(Connection conn, String command,
			Object[] parameters) {
		Map map = null;
		ResultSet resultset = null;
		try {
			resultset = executeQuery(conn, command, parameters);
			map = resultSetToMap(resultset);
		} catch (Exception ex) {
			throw new RuntimeException(ex.getMessage() + "(ִ��SQL��ѯ:" + command
					+ "����)", ex);
		} finally {
			try {
				if (resultset != null) {
					close(resultset, resultset.getStatement());
				}
			} catch (Exception ex) {
				log.error(ex);
			}
		}

		return map;
	}

	public static final Map getMap(String command) {
		return getMap(command, null);
	}

	public static final Map getMap(String command, Object key) {
		Object[] parameters = new Object[1];
		parameters[0] = key;

		return getMap(command, parameters);
	}

	public static final Map getMap(String command, Object key1, Object key2) {
		Object[] parameters = new Object[2];
		parameters[0] = key1;
		parameters[1] = key2;

		return getMap(command, parameters);
	}

	public static final Map getMap(String command, Object key1, Object key2,
			Object key3) {
		Object[] parameters = new Object[3];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;

		return getMap(command, parameters);
	}

	public static final Map getMap(Connection connection, String command,
			Object key1, Object key2, Object key3, Object key4) {
		Object[] parameters = new Object[4];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;

		return getMap(command, parameters);
	}

	public static final Map getMap(String command, Object key1, Object key2,
			Object key3, Object key4, Object key5) {
		Object[] parameters = new Object[5];
		parameters[0] = key1;
		parameters[1] = key2;
		parameters[2] = key3;
		parameters[3] = key4;
		parameters[4] = key5;

		return getMap(command, parameters);
	}

	

	/**
	 * ��ȡ��ǰ�û�
	 * 
	 * @return
	 * @throws Exception
	 */
	public static String getCurrentDbUser() throws Exception {
		String sSQL = " select user from dual";
		OracleCachedRowSet ocrs = getOracleCachedRowSet(sSQL);
		String result = "";
		if (ocrs.next()) {
			result = ocrs.getString("user");
		}

		return result;
	}

	private static void testExecuteSP() {
		Object[] aa = new Object[1];
		SpParameter sp = new SpParameter(4, "OUT", "x", null);
		Integer in = new Integer(1);
		try {
			aa = ExecuteSP("{ ?= call opcimp.createalarmrespinfo(?,?,?,?) }",
					sp, in,
					StrCharUtil.parseTimestamp("2004-03-01", "yyyy-MM-dd"),
					StrCharUtil.parseTimestamp("2004-03-31", "yyyy-MM-dd"), in);

			System.out.println("result=" + (String) aa[0]);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
}